const Router = require('koa-router');
const router = new Router();
const tool = require("../tools/tool");
const {db, QueryTypes} = require("../models/db");

// 导入文件
router.post('/excel/importFile', async ctx => {
    let list = ctx.request.body.list
    let tid = ctx.user.tenantId
    let uuid = tool.uuid()
    let arr = []
    list.map(row => {
        arr.push({
            tenantId: tid,
            name: tool.xe.trim(row.name),
            pinYin: tool.py(tool.xe.trim(row.name)),
            categoryName: row.categoryName?tool.xe.trim(tool.xe.toString(row.categoryName)):'',
            salesName: row.salesName?tool.xe.trim(tool.xe.toString(row.salesName)):'',
            salesNamePinYin: row.salesName?tool.py(tool.xe.trim(tool.xe.toString(row.salesName))):'',
            areaName: row.areaName?tool.xe.trim(tool.xe.toString(row.areaName)):'',
            address: row.address?tool.xe.trim(tool.xe.toString(row.address)):'',
            phone: row.phone ? tool.xe.trim(tool.xe.toString(row.phone)) : '',
            uuid: uuid,
        })
    });

    let sqladmin = `MERGE into admin a using 
(
select ${tid} tenantId, b.* from ( SELECT DISTINCT salesName,salesNamePinYin from contact_import_temp  where salesName <>'' ) b
) b on a.tenantId=b.tenantId and a.name=b.salesName 
when not matched then 
insert(tenantId,name,ctime,username,token,pinYin) values(b.tenantId,b.salesName,GETDATE(),NEWID(),NEWID(),b.salesNamePinYin);`

    let area = `MERGE into area a using 
(
select ${tid} tenantId, b.* from ( SELECT DISTINCT areaName from contact_import_temp  where areaName <>'') b
) b on a.tenantId=b.tenantId and a.name=b.areaName 
when not matched then 
insert(tenantId,name) values(b.tenantId,b.areaName);`
    let cate = `MERGE into contact_cat a using 
(
select ${tid} tenantId, b.* from ( SELECT DISTINCT categoryName from contact_import_temp  where categoryName<>'') b
) b on a.tenantId=b.tenantId and a.name=b.categoryName 
when not matched then 
insert(tenantId,name) values(b.tenantId,b.categoryName);`
    let contact = `merge into contact a using
(
SELECT DISTINCT ${tid} tenantId, a.name,c.id categoryId,a.categoryName,'' linkName,phone,address, ad.id salesId,b.id areaId,a.pinYin from contact_import_temp 
a left join area b on a.areaName=b.name left join contact_cat c on a.categoryName=c.name  left join admin ad on a.salesName=ad.name 
 ) b on a.tenantId=b.tenantId and a.name=b.name and a.address=b.address and a.linkPhone=b.phone and a.salesId=b.salesId 
 when not matched then 
 insert (tenantId,name,categoryId,categoryName,linkName,linkPhone,address,salesId,areaId, pinYin)
values(b.tenantId,b.name,b.categoryId,b.categoryName,'',b.phone,address,salesId,areaId, pinYin);`
    await db.transaction(async t => {
        let page = 0
        while (page * 1000 < arr.length) {
            let data = arr.slice(page*1000, page*1000+1000)
            if(data.length > 0) {
                await db.models['contactImportTemp'].bulkCreate(data, {transaction: t})
            }
            page++
        }
        await db.query(sqladmin, {type: QueryTypes.UPDATE}, {transaction: t})
        await db.query(area, {type: QueryTypes.UPDATE}, {transaction: t})
        await db.query(cate, {type: QueryTypes.UPDATE}, {transaction: t})
        await db.query(contact, {type: QueryTypes.UPDATE}, {transaction: t})
        await db.query(`delete from contact_import_temp where uuid='${uuid}'`)
    })
    return ctx.suc('已导入', {total: arr.length})
})
// 导入模板
router.post('/cxcel/importTemp', async ctx => {
    let data = [
        {
            name: '客户导入模板',
            data: [
                ['业务员', '店名', '客户类型', '地址', '电话','线路名称']
            ]
        }
    ]
    return tool.exportOut('客户导入模板',data, ctx)
})

module.exports = router;
